
if exists(select name from sysobjects where name = 'sp_SinhVienGetByMalop')
	drop proc sp_SinhVienGetByMalop
go
/*
	Created by: Phuoc NH
	Created on: 13/05/2013 (dd/mm/yyyy)
	Description: 
	--

	File:$sql-scripts/Proc.sp_SinhVienGetByMalop.sql
*/

create proc sp_SinhVienGetByMalop
@MaLop varchar(50)
as
begin
	--select MatKhau, DiaChiHT, Email, DienThoai, MaSinhVien,Ho, Ten, MaLop, CONVERT(varchar(12), NgaySinh, 103) as ngaysinh, GioiTinh, DanToc, TonGiao, CMTND, CONVERT(varchar(12), NgaySinh, 103) as ngaycap, NoiCap, HoKhau, NoiSinh, HoTenBo, NNBo, NNMe, HoTenMe, YEAR( NamTN12) as namtn12, SoHieuBang12, DiaChi  from SinhVien where MaSinhVien=@MaSinhVien and TrangThai<>'0'
	select MatKhau, DiaChiHT, Email, DienThoai, MaSinhVien,Ho, Ten, a.MaLop, CONVERT(varchar(12), NgaySinh, 103) as ngaysinh, GioiTinh, DanToc, TonGiao, CMTND, CONVERT(varchar(12), ngaycap, 103) as ngaycap, NoiCap, HoKhau, NoiSinh, HoTenBo, NNBo, NNMe, HoTenMe, YEAR( NamTN12) as namtn12, SoHieuBang12, DiaChi , e.MaKhoa from SinhVien a inner join Lop b on a.MaLop=b.MaLop and b.MaLop=@MaLop and a.TrangThai<>'0' inner join (ChuyenNganh c inner join nganh d on c.manganh=d.manganh inner join Khoa e on d.makhoa=e.MaKhoa) on b.MaCN=c.MaCN order by MaSinhVien
end

